﻿#include "sqliteutil.h"
#include <QProgressDialog>
#include <QApplication>
#include <QDate>
#include <QMessageBox>
#include <QStandardPaths>

QDataStream& operator >>(QDataStream& is, PDXP2& pdxp)
{
    is.setByteOrder(QDataStream::LittleEndian);
    is >> pdxp.VER >> pdxp.MID >> pdxp.SID >> pdxp.DID >> pdxp.BID >> pdxp.NO
            >> pdxp.FLAG >> pdxp.Res >> pdxp.JD >> pdxp.JS >> pdxp.LEN;
    is.skipRawData(pdxp.LEN);
    return is;
}


SqliteUtil::SqliteUtil()
{
    m_nRow = 0;
    m_nRowMax = 100;
    m_bDiscontinue = false;
    // 初始化
    // 获取默认的数据库文件地址
    //    m_strInitialPath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
    m_strInitialPath = QStandardPaths::writableLocation(QStandardPaths::HomeLocation);
    qDebug() << "initialPath:" << m_strInitialPath;
    // 查询是否有数据库文件,没有就创建一个 有就判断是否大小10G 如果大于10G 然后就删除当前DB文件 然后又重新创建一个

    quint64 size = 0;
    quint64 MaxSize = 10737418240;


    QFile fp;
    m_strInitialPath += "/info.db";
    fp.setFileName(m_strInitialPath);
    size = fp.size();
    if (fp.exists())
    {
        qDebug() << m_strInitialPath;
        if (size > MaxSize)
        {
            fp.remove();

            fp.open(QIODevice::ReadWrite);
            fp.close();
        }
    }
    else
    {
        qDebug() << "No file";
        fp.open(QIODevice::ReadWrite);
        fp.close();
    }
}

SqliteUtil::~SqliteUtil()
{
    //关闭数据库
    disConnection();
    return;
}

void SqliteUtil::init()
{
    createConnection(m_strInitialPath);
}

bool SqliteUtil::createConnection(QString dbname)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "conn_chart");
    if (!QSqlDatabase::drivers().contains("QSQLITE"))
        qDebug() << "drivers  Error!Unable to load databas!This App needs the SQLITE driver";
    db.setDatabaseName(dbname);
    QString strname = db.connectionName();
    if (!db.open()) {
        qDebug() << "open db Error!" << db.lastError();
        return false;
    }
    else {
        qDebug() << "open db success!" << strname;
        return true;
    }
}

void SqliteUtil::disConnection()
{
    {
        QSqlDatabase db = QSqlDatabase::database("conn_chart");
        QSqlQuery query("SELECT NAME, DOB FROM EMPLOYEES", db);
    }
    // Both "db" and "query" are destroyed because they are out of scope
    QSqlDatabase::removeDatabase("conn_chart"); // correct
    qDebug() << "disConnection db success!";
}

bool SqliteUtil::createTabel(QString fileName)
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    QString tbname = QFileInfo(fileName).fileName();
    QStringList list = tbname.split(".");
    m_sTbname = list[0];
    strToSql(m_sTbname);
    file.setFileName(fileName);
    QString create_sql = QString("create table %1 (%2 int)").arg(m_sTbname).arg("js");
    if (!sql_query.exec(create_sql))
    {
        qDebug() << "Error: Fail to create table." << sql_query.lastError();
        return false;
    }
    else
    {
        qDebug() << "Table created!";
        return true;
    }
}

bool SqliteUtil::isExistTab(QString tbname)
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);

    QString fname = QFileInfo(tbname).fileName();
    QStringList list = fname.split(".");
    m_sTbname = list[0];
    strToSql(m_sTbname);
    QString strSql = QString("select count(*) from sqlite_master where name='%1'").arg(m_sTbname);

    if (!sql_query.exec(strSql))
    {
        return false;
    }
    else
    {
        while (sql_query.next())
        {
            int id = sql_query.value(0).toInt();
            if (id)
            {
                return true;
            }
            else {
                return false;
            }
        }
        return false;
    }
}


void SqliteUtil::insertData(QVector<qint64> records)
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    sql_query.exec("BEGIN;");
    file.open(QIODevice::ReadOnly);
    PDXP2 pdxpNew;
    PDXP2 pdxpOld;
    QByteArray record;
    m_nRow = 0;
    m_nRowMax = records.size();
    qint64 pos = 0;
    int size = 0;
    if (m_bDiscontinue)
    {
        file.close();
        m_nRow = 0;
        m_nRowMax = 100;
        sql_query.exec("COMMIT;");
        dropTab(m_sTbname);
        return;
    }
    for (int row = 0; row < records.size(); row++) {
        if (m_bDiscontinue)
        {
            file.close();
            m_nRow = 0;
            m_nRowMax = 100;
            sql_query.exec("COMMIT;");
            dropTab(m_sTbname);
            return;
        }
        m_nRow++;
        file.seek(pos);
        size = records.at(row) - pos;
        record = file.read(size);
        QDataStream inNew(record);
        inNew >> pdxpNew;

        if (row + 1 == records.size())
        {
            break;
        }
        pos = records.at(row);
        file.seek(pos);
        size = records.at(row + 1) - pos;
        record = file.read(size);
        QDataStream inOld(record);
        inOld >> pdxpOld;

        QString info = QString("INSERT INTO %1 VALUES(%2)").arg(m_sTbname).arg(QString::number((pdxpOld.JS - pdxpNew.JS) / 10));

        if (!sql_query.exec(info))
        {
            qDebug() << sql_query.lastError();
        }
        else
        {
        }
    }
    file.close();
    m_nRow = 0;
    m_nRowMax = 100;
    bool transaction_End = sql_query.exec("COMMIT;");
    qDebug() << " transaction_End : " << transaction_End;
}

void SqliteUtil::selectData()
{
    //查询数据
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    sql_query.exec("select * from student");
    if (!sql_query.exec())
    {
        qDebug() << sql_query.lastError();
    }
    else
    {
        while (sql_query.next())
        {
            int id = sql_query.value(0).toInt();
            QString name = sql_query.value(1).toString();
            int age = sql_query.value(2).toInt();
            qDebug() << QString("id:%1    name:%2    age:%3").arg(id).arg(name).arg(age);
        }
    }

}

int SqliteUtil::getMaxValue()
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    int nMax = 0;
    QString strSql = QString("SELECT max(js) FROM %1").arg(m_sTbname);
    if (!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
        return 0;
    }
    else
    {
        while (sql_query.next())
        {
            nMax = sql_query.value(0).toInt();
        }
    }
    return nMax;
}

int SqliteUtil::getMinValue()
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    int nMin = 0;
    QString strSql = QString("SELECT min(js) FROM %1").arg(m_sTbname);
    if (!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
        return 0;
    }
    else
    {
        while (sql_query.next())
        {
            nMin = sql_query.value(0).toInt();
        }
    }
    return nMin;
}

int SqliteUtil::getAllCount()
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    int count = 0;
    QString strSql = QString("SELECT count(*) FROM %1").arg(m_sTbname);
    if (!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
        return 0;
    }
    else
    {
        while (sql_query.next())
        {
            count = sql_query.value(0).toInt();
        }
    }
    return count;

    return 0;
}

double SqliteUtil::getVariance()
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    float fVariance = 0;
    QString strSql = QString("SELECT AVG((%1.js - sub.a) * (%2.js - sub.a)) as var from %3,(SELECT AVG(js) AS a FROM %4) AS sub").arg(m_sTbname).arg(m_sTbname).arg(m_sTbname).arg(m_sTbname);
    if (!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
        return 0;
    }
    else
    {
        while (sql_query.next())
        {
            fVariance = sql_query.value(0).toFloat();
        }
    }
    return qSqrt(fVariance);
}

double SqliteUtil::getAvgValue()
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    double fAvg = 0;
    if (db.isOpen()) {
        QSqlQuery sql_query(db);
        QString strSql = QString("SELECT avg(js) FROM %1").arg(m_sTbname);
        if (!sql_query.exec(strSql))
        {
            qDebug() << sql_query.lastError();
            return 0;
        }
        else
        {
            while (sql_query.next())
            {
                fAvg = sql_query.value(0).toFloat();
            }
        }
    }
    return fAvg;
}



int SqliteUtil::selectRangeCount(int min, int max)
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    QString strSql = QString("select count(*) from %1 where js <= %2 and js >= %3").arg(m_sTbname).arg(max).arg(min);
    int nCount = 0;
    if (!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
        return 0;
    }
    else
    {
        while (sql_query.next())
        {
            nCount = sql_query.value(0).toFloat();
        }
    }
    return nCount;

}

QMap<int, int> SqliteUtil::selectRepetRecords()
{
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QMap<int, int> records;
    QSqlQuery sql_query(db);
    QString strSql = QString("select js, count(*) from %1 group by js").arg(m_sTbname);
    if (!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
        return records;
    }
    else
    {
        while (sql_query.next())
        {
            records.insert(sql_query.value(0).toFloat(), sql_query.value(1).toFloat());
        }
    }
    return records;
}

void SqliteUtil::dropTab(QString tbname)
{
    //删除表格
    QSqlDatabase db = QSqlDatabase::database("conn_chart");
    QSqlQuery sql_query(db);
    QString strSql = QString("drop table %1").arg(m_sTbname);
    if (!sql_query.exec(strSql))
    {
        qDebug() << sql_query.lastError();
    }
    else
    {
        qDebug() << "table cleared";
    }
}

void SqliteUtil::strToSql(QString& str)
{
    str = str.replace(" ", "");
    str = str.replace("-", "0");
    str = str.replace("+", "0");
}

QString SqliteUtil::jsLineString(quint32 js)
{
    QTime time(0, 0);
    return QString("%1%2").arg(time.addMSecs(js / 10).toString("HH:mm:ss.zzz")).arg(js % 10);
}

void SqliteUtil::getProssBarValue(int& row, int& rowMax)
{
    row = m_nRow;
    rowMax = m_nRowMax;
}
